﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Threading;
using NPOI.SS.Formula.Functions;

namespace Utilities.IO.EXCEL
{
    public class NpoiHelper
    {
        private IWorkbook _workbook;
        private ISheet _sheet;
        private int _maxRowNum;
        private int _maxColNum;
        public IWorkbook Workbook
        {
            get
            {
                return _workbook;
            }
        }
        public ISheet Sheet
        {
            get
            {
                return _sheet;
            }
        }
        public int MaxRowNum
        {
            get
            {
                return _maxRowNum;
            }
        }
        public int MaxColNum
        {
            get
            {
                return _maxColNum;
            }
        }
        public NpoiHelper(string filePath, int sheetIndex)
        {
            if (File.Exists(filePath))//存在
            {
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite,FileShare.Read))
                {
                    _workbook = GetWorkbookInstance(fs, filePath);
                    if (_workbook != null)
                    {
                        _sheet = _workbook.GetSheetAt(sheetIndex);
                    }
                }
            }
            else//不存在 创建新文件时 一定要先实例化空的workbook 再写入文件流！！
            {
                _workbook = GetWorkbookInstance(null, filePath);
                if (_workbook != null)
                {
                    _sheet = _workbook.GetSheetAt(sheetIndex);
                }
                //using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                //{
                //    _workbook.Write(fs);
                //}
            }
        }

        #region PRIVATE
        /// <summary>
        /// 获取Workbook的实例
        /// </summary>
        /// <param name="fs">Stream类型</param>
        /// <param name="filePath">文件路径</param>
        /// <returns>workbook实例</returns>
        private IWorkbook GetWorkbookInstance(Stream fs, string filePath)
        {
            IWorkbook workbook = null;
            if (filePath.IndexOf(".xlsx") > 0)
            {
                if (File.Exists(filePath))//存在
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else//不存在
                {
                    workbook = new XSSFWorkbook();
                    workbook.CreateSheet("Sheet1");
                }
            }
            else if (filePath.IndexOf(".xls") > 0)
            {
                if (File.Exists(filePath))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = new HSSFWorkbook();
                    workbook.CreateSheet("Sheet1");
                }
            }
            else
            {
                return null;
            }
            return workbook;
        }

        /// <summary>
        /// 获取表格数据
        /// </summary>
        /// <param name="sheet">根据workbook获取sheet</param>
        /// <param name="maxRow">最大行</param>
        /// <param name="maxCol">最大列</param>
        /// <returns>表格数据</returns>
        private string[,] GetData(int maxRow, int maxCol)
        {
            string[,] tabData = new string[maxRow, maxCol];
            Task task = new Task(() =>
            {
                string str = "";
                ICell cell;
                //遍历所有的数据
                for (int i = 0; i < maxRow; i++)
                {
                    for (int j = 0; j < maxCol; j++)
                    {
                        if (_sheet.GetRow(i) != null)//这里为了预防碰到未激活的单元格 所以这里判断一定要加!!!
                        {
                            try
                            {
                                cell = _sheet.GetRow(i).GetCell(j);
                                if (cell != null)
                                {
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank:
                                            str = "";
                                            break;
                                        case CellType.Boolean:
                                            str = cell.BooleanCellValue ? "是" : "否";
                                            break;
                                        case CellType.Numeric:
                                            str = cell.ToString();
                                            break;
                                        case CellType.String:
                                            str = cell.StringCellValue;
                                            break;
                                        case CellType.Formula:
                                            try
                                            {
                                                str = cell.NumericCellValue.ToString();
                                            }
                                            catch
                                            {
                                                str = cell.StringCellValue;
                                            }
                                            break;
                                        case CellType.Error:
                                            str = "";
                                            break;
                                        default:
                                            str = "=" + cell.CellFormula;
                                            break;
                                    }
                                }
                                else
                                {
                                    str = "";
                                }
                            }
                            catch { str = ""; }
                            finally
                            {
                                tabData[i, j] = str;
                            }
                        }
                        else
                        {
                            tabData[i, j] = "";
                        }
                    }
                }
            });
            task.RunSynchronously();
            return tabData;
        }

        /// <summary>
        /// 写入表格数据[单个]
        /// </summary>
        /// <param name="sheet">根据workbook获取sheet</param>
        /// <param name="data">数据</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        private void SetData(string data, int row, int col)
        {
            //npoi的CreatRow方法创建时会把整个一行数据都会清空
            LastPosition lastPos = new LastPosition();
            lastPos = GetLastPosition();
            IRow irow = null;
            if (row > lastPos.lastRow || lastPos.lastCol == 0)//输入行比最大行大 则创建新的一行
            {
                irow = _sheet.CreateRow(row);
            }
            else//反之则获取用户输入的行数
            {
                irow = _sheet.GetRow(row);
            }
            if(irow == null)
                irow = _sheet.CreateRow(row);
            var cell =irow.GetCell(col);
            if (cell == null)
                cell = irow.CreateCell(col);
            cell.SetCellValue(data.ToString());
        }

        #endregion



        public void ChangeSheet(string name)
        {
            _sheet = _workbook.GetSheet(name);
        }


        /// <summary>
        /// 获取单个单元格数据
        /// </summary>
        /// <param name="rowIndex">第几行</param>
        /// <param name="colIndex">第几列</param>
        /// <returns>字符串形式的数据</returns>
        public string GetCellData(int rowIndex, int colIndex)
        {
            try
            {
                //CellType type = _sheet.GetRow(rowIndex).GetCell(colIndex).CellType;
                //if (type != CellType.Blank)
                //{
                string cellStr = _sheet.GetRow(rowIndex).GetCell(colIndex).StringCellValue;
                if (cellStr == null)
                {
                    return "";
                }
                return cellStr;
                //}
                //return "";
            }
            catch
            {
                return "ERROR!!!";
            }
        }



        /// <summary>
        /// 根据已知的行列数来获取Excel表内容
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="maxRow">最大行</param>
        /// <param name="maxCol">最大列</param>
        /// <returns>表格内容</returns>
        public string[,] GetSheetData(int maxRow, int maxCol)
        {
            return GetData(maxRow, maxCol);
        }


        /// <summary>
        /// 根据文件路径访问指定Sheet的全部内容
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <returns>表格内容</returns>
        public string[,] GetSheetData()
        {
            LastPosition lstPos = GetLastPosition();
            return GetData(lstPos.lastRow, lstPos.lastCol);
        }

        /// <summary>
        /// 根据文件路径访问指定Sheet的内容
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="data">数据</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        /// <param name="sheetName">Sheet名称，如果忽略不写会变成文件名</param>
        public void SetSheetData(string data, int row, int col)
        {
            SetData(data, row, col);
        }


        /// <summary>
        /// 保存当前Excel文件
        /// </summary>
        /// <param name="filePath">保存的文件路径</param>
        public void SaveWorkbook(string filePath)
        {
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                _workbook.Write(fs);
            }
        }


        /// <summary>
        /// 获取Excel的最大行最大列
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <returns>最大行数，列数</returns>
        public LastPosition GetLastPosition()
        {
            LastPosition lstPosition = new LastPosition();
            int maxCol = 0;
            int tempCol = 0;
            Task task = new Task(() =>
            {
                lstPosition.lastRow = _sheet.LastRowNum + 1;
                for (int i = 0; i < lstPosition.lastRow; i++)
                {
                    IRow headerRow = _sheet.GetRow(i);
                    if (headerRow != null)
                    {
                        tempCol = headerRow.LastCellNum;
                        if (tempCol > maxCol)
                        {
                            maxCol = tempCol;
                        }
                    }
                }
                lstPosition.lastCol = maxCol;
                //IRow headerRow = _sheet.GetRow(0);
                //lstPosition.lastCol = headerRow.LastCellNum;

            });
            task.RunSynchronously();
            return lstPosition;
        }
    }

    /// <summary>
    /// Excel的最大行最大列
    /// </summary>
    public struct LastPosition
    {
        public int lastRow;
        public int lastCol;
    }

    /// <summary>
    /// 单元格位置
    /// </summary>
    public struct CellPosition
    {
        public int rowIndex;
        public int colIndex;
    }
}
